package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.*;
import cn.edu.sdjzu.xg.bysj.service.DegreeService;
import cn.edu.sdjzu.xg.bysj.service.DepartmentService;
import cn.edu.sdjzu.xg.bysj.service.ProfTitleService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class TeacherDao {
    private static TeacherDao teacherDao = new TeacherDao();
    private static Collection<Teacher> teachers;

    private TeacherDao() {
    }

    public static TeacherDao getInstance() {
        return teacherDao;
    }

    /**
     * @param connection    数据库连接对象
     * @param conditionList 查询条件
     * @param pagination    分页对象
     * @return
     * @throws SQLException
     */
    public Collection<Teacher> findAll(Pagination pagination
            , List<Condition> conditionList,Connection connection) throws SQLException {
        //创建Teacher类型集合类对象，HashSet不保证结果中元素的顺序
        Collection<Teacher> desiredTeachers = new HashSet<Teacher>();
        //创建查询教师的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM teacher ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        //获取Teacher中的元组总数
        int totalNum = CommonDao.getInstance().count(connection, "teacher");
        if(pagination != null){
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }
        //获得查询教师的语句对象
        PreparedStatement pstmt_select =
                connection.prepareStatement(sql_select.toString());
        //获得教师对象的结果集
        ResultSet resultSet_select = pstmt_select.executeQuery();
        //遍历结果集，根据每个元素创建对象，并添加到集合类对象desiredTeachers中
        while(resultSet_select.next()) {
            //获得一个teacher对象中相应的字段
            int id = resultSet_select.getInt("id");
            String name = resultSet_select.getString("name");
            String no = resultSet_select.getString("no");
            //通过id查找相应的对象
            int profTitleId = resultSet_select.getInt("profTitle_id");
            ProfTitle profTitle = ProfTitleService.getInstance().find(profTitleId);
            int degreeId = resultSet_select.getInt("degree_id");
            Degree degree = DegreeService.getInstance().find(degreeId);
            int departmentId = resultSet_select.getInt("department_id");
            Department department = DepartmentService.getInstance().find(departmentId);
            //被添加进集合中的教师对象
            Teacher desiredTeacher = new Teacher(id, name, no, profTitle, degree, department);
            //将查找出的对象放入集合
            desiredTeachers.add(desiredTeacher);
        }
        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet_select, pstmt_select);
        return desiredTeachers;
    }
    /*
     *寻找单个对象方法
     *根据id寻找教师对象
     */
    public Teacher find(Integer id, Connection connection) throws SQLException {
        //创建Teacher类型的引用，暂不创建对象
        Teacher desiredTeacher = null;
        //预编译语句
        String sql_select_id = "SELECT * FROM teacher WHERE id = ?";
        PreparedStatement pstmt_select_id =
                connection.prepareStatement(sql_select_id);
        //为预编译参数赋值
        pstmt_select_id.setInt(1,id);
        //执行查询并获取结果集对象
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        if(resultSet_select_id.next()) {
            String name = resultSet_select_id.getString("name");
            String no = resultSet_select_id.getString("no");
            int profTitleId = resultSet_select_id.getInt("profTitle_id");
            ProfTitle profTitle = ProfTitleService.getInstance().find(profTitleId);
            int degreeId = resultSet_select_id.getInt("degree_id");
            Degree degree = DegreeService.getInstance().find(degreeId);
            int departmentId = resultSet_select_id.getInt("department_id");
            Department department = DepartmentService.getInstance().find(departmentId);
            //获取查询对象并用desiredTeacher指向
            desiredTeacher = new Teacher(id, name, no, profTitle, degree, department);
        }
        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet_select_id, pstmt_select_id);
        return desiredTeacher;
    }
    /*
     *修改方法
     *修改数据库中的教师记录
     */
    public boolean update(Teacher teacher,Connection connection) throws SQLException {
        //创建预编译语句
        String update_sql = "UPDATE teacher SET name = ?,no = ?,profTitle_id=?," +
                "degree_id=?,department_id=?" + " WHERE id = ?";
        //将预编译语句放入预编译语句盒子
        PreparedStatement pstmt = connection.prepareStatement(update_sql);
        //为预编译参数赋值
        pstmt.setInt(6,teacher.getId());
        pstmt.setString(1,teacher.getName());
        pstmt.setString(2,teacher.getNo());
        pstmt.setInt(3,teacher.getProfTitle().getId());
        pstmt.setInt(4,teacher.getDegree().getId());
        pstmt.setInt(5,teacher.getDepartment().getId());
        //执行语句，并获得结果
        int affectedRow = pstmt.executeUpdate();
        //关闭statement资源
        JdbcHelper.close(pstmt,null);
        return affectedRow>0;
    }

    /**
     * 由于Teacher扩展了Actor，前者对象id取后者的id的值，故参数teacher是有id的
     * 用于事务。调用本方法的Service，在调用本方法后，还会调用UserDao.add方法
     * 两次对Dao的调用要保证是同一个Connection对象，才能将它们设置在同一事务中
     * @param teacher    要增加到数据库表中的Teacher对象
     * @param connection 来自Service的Connection对象
     * @throws SQLException
     */
    public Boolean add(Teacher teacher, Connection connection) throws SQLException {
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement preparedStatement =
                connection.prepareStatement(
                        "INSERT INTO teacher " +
                                " (id, name,no, profTitle_id, degree_id, department_id)" +
                                " VALUES (?, ?, ?,?,?,?)");
        //为预编译参数赋值
        preparedStatement.setInt(1, teacher.getId());
        preparedStatement.setString(2, teacher.getName());
        preparedStatement.setString(3, teacher.getNo());
        preparedStatement.setInt(4, teacher.getProfTitle().getId());
        preparedStatement.setInt(5, teacher.getDegree().getId());
        preparedStatement.setInt(6, teacher.getDepartment().getId());
        //执行添加操作
        int affectedRowNum =preparedStatement.executeUpdate();
        //本方法是事务的一部分，不能释放数据库连接
        JdbcHelper.close(null,preparedStatement);
        if (  affectedRowNum ==1) {
            return true;
        }else {
            return false;
        }
    }

    public boolean delete(Integer id, Connection connection) throws SQLException {
        //根据连接对象准备语句对象
        PreparedStatement preparedStatement = connection.prepareStatement(
                "DELETE FROM teacher WHERE id = ?");
        //为预编译参数赋值
        preparedStatement.setInt(1,id);
        //执行删除操作
        int affectedRow = preparedStatement.executeUpdate();
        //关闭statement资源
        JdbcHelper.close(preparedStatement,null);
        return affectedRow>0;
    }
    public int countAll(Department department, Connection conn) throws SQLException{
        String str_sql="SELECT COUNT(id) as cnt_by_department_id FROM teacher WHERE department_id=?";
        PreparedStatement pstmt=conn.prepareStatement(str_sql);
        //为预编译参数赋值
        pstmt.setInt(1, department.getId());
        ResultSet resultSet=pstmt.executeQuery();
        int count=0;
        while (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        JdbcHelper.close(resultSet,pstmt);
        return count;
    }
}